package com.taskinception.connectivity;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.taskinception.R;
import com.taskinception.TIApplication;
import com.taskinception.model.Task;

import java.util.ArrayList;

/**
 * 
 * @author Remy Parzinski
 * 
 */
public class DatabaseAdapter {

    /**
     * Convenience class for the column names of the Task table
     * 
     * @author Remy Parzinski
     */
    public static final class TASK {
	public static final String ROWID = "_id";
	public static final String TITLE = "title";
	public static final String BODY = "body";
	public static final String OWNER = "owner";
	public static final String CREATED_DATE = "creationDate";
	public static final String LAST_MODIFIED_DATE = "lastModifiedDate";
	public static final String LAST_MODIFIER = "lastModifier";
	public static final String IS_DONE = "done";
	public static final String PARENT = "parent";

	public static final String REMOTE_ID = "remoteId";
	public static final String SHARED_WITH = "sharedWith";

	public static final String[] TABLE_KEYS = new String[] { ROWID, TITLE,
		BODY, OWNER, CREATED_DATE, LAST_MODIFIED_DATE, LAST_MODIFIER,
		IS_DONE, PARENT, REMOTE_ID };
	public static final String TABLE_NAME = "task";
	// TODO: Generate the TABLE_CREATE string
	public static final String TABLE_CREATE = String
		.format("CREATE TABLE %s (%s INTEGER PRIMARY KEY AUTOINCREMENT, %s TEXT NOT NULL, %s TEXT NOT NULL, %s TEXT NOT NULL, %s INTEGER NOT NULL, %s INTEGER NOT NULL, %s TEXT NOT NULL, %s INTEGER NOT NULL, %s INTEGER NOT NULL, %s TEXT NOT NULL);",
			TABLE_NAME, ROWID, TITLE, BODY, OWNER, CREATED_DATE,
			LAST_MODIFIED_DATE, LAST_MODIFIER, IS_DONE, PARENT,
			REMOTE_ID);
    }

    public static final String DB_NAME = "TaskInceptor";

    /**
     * Database version. Increment by 1 (one) when making changes to the
     * database structure
     */
    private static final int DB_VERSION = 1;

    /**
     * Instance of the {@link DatabaseHelper} class
     */
    private DatabaseHelper mDbHelper;

    /**
     * Instance of a {@link SQLiteDatabase}
     */
    private SQLiteDatabase mDb;

    /**
     * Instance of the {@link Context} being passed to the
     * {@link DatabaseHelper} class
     */
    private final Context mCtx;

    /**
     * Constructor for creating ans instance of the DatabaseAdapter. Pass the
     * current {@link Activity} ({@code this}) as {@link Context}
     * 
     * @param ctx
     *            {@link Context} to pass to {@link DatabaseHelper}
     */
    public DatabaseAdapter(Context ctx) {
	this.mCtx = ctx;
    }

    /**
     * Open the database connection.
     * {@link DatabaseHelper#onCreate(SQLiteDatabase) Create a database} when
     * not yet available.
     * 
     * @return {@code this} for chaining of initialization
     * @throws SQLException
     *             when no database can be instantiated
     */
    public DatabaseAdapter open() throws SQLException {
	mDbHelper = new DatabaseHelper(mCtx);
	mDb = mDbHelper.getWritableDatabase();
	return this;
    }

    /**
     * Close the database object. There is no connection after this call
     */
    public void close() {
	mDbHelper.close();
    }

    /**
     * Insert a new Task into the database and return the generated id
     * 
     * @param task
     *            {@link com.taskinception.model.Task Task} to insert
     * @return generated id of the inserted task
     */
    public long createTask(Task task) {
	return mDb.insert(TASK.TABLE_NAME, "", task.getContentValues());
    }

    /**
     * Delete a task with the given rowId
     * 
     * @param rowId
     *            The id of the task to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteTask(long rowId) {
        if (mDb.delete(TASK.TABLE_NAME, TASK.ROWID + " = ?",
            new String[] { String.valueOf(rowId) }) > 0) {
            return TIApplication.getInstance().deleteTaskFromTaskTree(rowId);
        }
        return false;
    }

    /**
     * Return a {@code Cursor} over the list of all child tasks from given
     * parent
     * 
     * @param parent
     *            The rowId of the parent task
     * @return {@code Cursor} over all child tasks
     */
    public Cursor fetchFromParent(Task parent) {
	return mDb.query(
		TASK.TABLE_NAME,
		TASK.TABLE_KEYS,
		TASK.PARENT + " = ?" + " AND " + TASK.ROWID + " != ?",
		new String[] { String.valueOf(parent.getId()),
			String.valueOf(parent.getId()) }, null, null, null);
    }

    // public ArrayList<Task> fetchFromParentAsList(Task parent) {
    // return fetchFromParentAsList(parent, false);
    // }

    /**
     * Fetch the children of {@code parent} from the database and put them in an
     * {@link java.util.ArrayList ArrayList}<
     * {@link com.taskinception.model.Task Task}>
     * 
     * @param parent
     *            {@link com.taskinception.model.Task Task} from which to get
     *            the children
     * @return list of {@code parent}'s children
     */
    public ArrayList<Task> fetchFromParentAsList(Task parent) {
	Cursor cursor = fetchFromParent(parent);
	ArrayList<Task> taskList = new ArrayList<Task>();
	if (cursor.moveToFirst()) {
	    Task task = null;
	    do {
		task = fetchTask(cursor.getLong(cursor
			.getColumnIndexOrThrow(TASK.ROWID)));
		taskList.add(task);
	    } while (cursor.moveToNext());
	    cursor.close();
	}
	return taskList;
    }

    /**
     * Return a {@code Cursor} positioned at the task that matches the given
     * {@code rowId}
     * 
     * @param rowId
     *            the id of the the {@link com.taskinception.model.Task Task} to
     *            fetch
     * @return Cursor positioned to the matching
     *         {@link com.taskinception.model.Task Task}, if found
     * @throws SQLException
     *             if the {@link com.taskinception.model.Task Task} could not be
     *             found
     */
    public Cursor fetchTaskCursor(long rowId) throws SQLException {
	Cursor cursor = mDb.query(true, TASK.TABLE_NAME, TASK.TABLE_KEYS,
		TASK.ROWID + "=?", new String[] { String.valueOf(rowId) },
		null, null, null, null);
	if (cursor != null) {
	    cursor.moveToFirst();
	}
	return cursor;
    }

    /**
     * Obtain a {@link com.taskinception.model.Task Task} from the database
     * based on the given {@code rowId}
     * 
     * @param rowId
     *            id of the {@link com.taskinception.model.Task Task} to fetch
     * @return Task based on the given {@code rowId}, if found
     * @throws SQLException
     *             if the {@link com.taskinception.model.Task Task} could not be
     *             found
     */
    public Task fetchTask(long rowId) throws SQLException {
	Task task = new Task(fetchTaskCursor(rowId));
	task.setChildren(fetchFromParentAsList(task));
	return task;
    }

    /**
     * Save the current state of {@code task} to the database
     * 
     * @param task
     *            {@link com.taskinception.model.Task Task} to update
     * @return whether the action succeeded or not
     */
    public boolean updateTask(Task task) {
	return mDb.update(TASK.TABLE_NAME, task.getContentValues(), TASK.ROWID
		+ "=?", new String[] { String.valueOf(task.getId()) }) > 0;
    }

    /**
     * Private class to handle the database. It extends the
     * {@link SQLiteOpenHelper} and overrides the
     * {@code onCreate(SQLiteDatabase)} and
     * {@code onUpgrade(SQLiteDatabase, int, int)} methods to determine how to
     * behave when there is no database available or when there is a new version
     * 
     * @author Remy Parzinski
     * @see {@link SQLiteOpenHelper}
     */
    private static class DatabaseHelper extends SQLiteOpenHelper {

	Context _context;

	/**
	 * Constructor - create an instance of the {@link DatabaseHelper} and
	 * run it in the provided {@code Context}
	 * 
	 * @param ctx
	 *            {@code Context} to run it in
	 */
	public DatabaseHelper(Context ctx) {
	    super(ctx, DB_NAME, null, DB_VERSION);
	    _context = ctx;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	    Log.v("onCreate", "Creating database structure");
	    db.execSQL(TASK.TABLE_CREATE);
	    Log.v("onCreate", "Database stucture created");

	    ContentValues root = new Task(
		    _context.getString(R.string.main_task_title),
		    _context.getString(R.string.main_task_body))
		    .getContentValues();
	    root.put(TASK.ROWID, 0);

	    long id = db.insert(TASK.TABLE_NAME, "", root);
	    Log.v("onCreate", "Root Task inserted: " + id);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	    Log.w(this.getClass().getSimpleName(), String.format(
		    "Upgrading database from version %d to %d.", oldVersion,
		    newVersion));
	    /*
	     * TODO (remy): Before dropping the tables, first store all the
	     * saved data to prevent dataloss. Maybe a copy of the current task
	     * tree can be made, which is stored in TIApplication.
	     */
	    db.execSQL(String.format("DROP TABLE IF EXISTS %s;", DB_NAME));
	    onCreate(db);
	    // TODO (remy): Insert all the stored values
	}

    }
}
